/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pkg_productManagement;

import java.sql.*;
import java.util.ArrayList;
import pkg_group5.Database;


/**
 *
 * @author mattcarr
 */
public class ProductDatabaseFunctions extends Database{
    
    public ProductDatabaseFunctions() {
        super("eBookSystem");
    }

    public ArrayList<Product> getProducts() {
        
        String query = "SELECT * from products";
        
        ArrayList<Product> products = new ArrayList();
        
        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Product p = new Product(rs.getInt(1));
                p.setName(rs.getString(2));
                p.setDesc(rs.getString(3));
                p.setPrice(rs.getDouble(4));
                p.setStock(rs.getInt(5));
                p.setImage(rs.getString(6));
                p.setGenre(rs.getString(7));
                
                products.add(p);
            }
            
            return products;
        } catch (Exception e) {
            System.out.println(e);
            return null;
        }
    }
    
     public ArrayList<Product> getProductsByCategory(String Category) {
        
        String query = "SELECT * from products WHERE category = ?";
        
        ArrayList<Product> products = new ArrayList();
        
        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setString(1, Category);
            
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Product p = new Product(rs.getInt(1));
                p.setName(rs.getString(2));
                p.setDesc(rs.getString(3));
                p.setPrice(rs.getDouble(4));
                p.setStock(rs.getInt(5));
                p.setImage(rs.getString(6));
                p.setGenre(rs.getString(7));
                products.add(p);
            }
            
            return products;
        } catch (Exception e) {
            System.out.println(e);
            return null;
        }
    }
      public Product getProductsByID(int ID) {
        
        String query = "SELECT * from products WHERE product_id = ?";
        
        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, ID);
            
            ResultSet rs = ps.executeQuery();
            Product p = new Product(ID);
            while (rs.next()) {
                p.setName(rs.getString(2));
                p.setDesc(rs.getString(3));
                p.setPrice(rs.getDouble(4));
                p.setStock(rs.getInt(5));
                p.setImage(rs.getString(6));
                p.setGenre(rs.getString(7));
            }
            
            return p;
        } catch (Exception e) {
            System.out.println(e);
            return null;
        }
    }
}
